﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using CommontLibrary;
using DAL;
/// <summary>
/// Summary description for ExcelHelper
/// </summary>
public class ExcelHelper
{
    public ExcelHelper()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    #region Reading From Excel File
    public static void uploadXLFile(FileUpload fileUpload, string mPath)
    {
        mPath = mPath + fileUpload.FileName.ToString();
        fileUpload.SaveAs(mPath);
    }
    private static string transform_string_vi_date_2_en_date(string ip_str_vi_date)
    {
        string v_str_en_date = "";
        string[] v_str_vi_date_array = ip_str_vi_date.Split('/');
        v_str_en_date += v_str_vi_date_array[1] + "/" + v_str_vi_date_array[0] + "/" + v_str_vi_date_array[2];
        return v_str_en_date;
    }
    //CReated by Linhdh 03/05/2012
    public static void uploadXLFileWithFileName(FileUpload fileUpload, string mPath, string filename)
    {
        mPath = mPath + filename;
        fileUpload.SaveAs(mPath);
    }
    public void uploadXLFile(FileUpload fileUpload, string mPath, string IPAdr)
    {
        mPath = mPath + fileUpload.FileName.ToString().Insert(fileUpload.FileName.ToString().Length - 4, IPAdr.Replace(".", "-"));
        fileUpload.SaveAs(mPath);
    }

    public static string[] getExcelSheets(string mFile)
    {
        try
        {
            string strXlsConnString;
            strXlsConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbConnection xlsConn = new OleDbConnection(strXlsConnString);
            xlsConn.Open();
            DataTable xlTable = new DataTable();
            xlTable = xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            System.String strExcelSheetNames = "";
            string sheetName;
            //Loop through the excel database table names and take only the tables that ends with a $ characters. Other tables are not worksheets...
            for (int lngStart = 0; lngStart < xlTable.Rows.Count; lngStart++)
            {
                sheetName = xlTable.Rows[lngStart][2].ToString().Replace("'", ""); //Remove the single-quote surrounding the table name...
                if (sheetName.EndsWith("$")) //Yes, this is a worksheet
                {
                    strExcelSheetNames += sheetName.Substring(0, sheetName.Length - 1) + "~"; //concatenate with a single-quote delimeter... to be returned as a string array later using the split function
                }
            }

            if (strExcelSheetNames.EndsWith("~")) //the last single quote needs to be removed so that the array index ends with the last sheetname
            {
                strExcelSheetNames = strExcelSheetNames.Substring(0, strExcelSheetNames.Length - 1);
            }

            xlsConn.Close();
            xlsConn.Dispose();
            char[] chrDelimter = { '~' };
            return strExcelSheetNames.Split(chrDelimter);
        }
        catch (Exception exp)
        {
            throw new Exception("Error while listing the excel sheets from upload file <br>" + exp.Message, exp);
        }
    }
    //public static DataTable getXLDataCRM1(string xlSheetName, string xlFileName, string AdditionalFields, bool check, int NguoiTimNguonID)
    //{
    //    try
    //    {
    //        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
    //        OleDbConnection xlConn = new OleDbConnection(connstr);
    //        DataSet xlTDS = new DataSet("xlDataSet");
    //        xlConn.Open();
    //        OleDbDataAdapter xlDA = new OleDbDataAdapter("Select" + AdditionalFields + " * from [" + xlSheetName + "$] ", xlConn);
    //        xlDA.Fill(xlTDS);
    //        xlConn.Close();
    //        xlConn.Dispose();
    //        RemoveEmptyRows(xlTDS.Tables[0], (AdditionalFields.Length - AdditionalFields.ToLower().Replace(" as ", "").Length) / 4);
    //        // create new table and call it rssItems
    //        DataTable dtItems = new DataTable("rssItems");
    //        DataColumn dcItem = new DataColumn(); dcItem.ColumnName = "ID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dcItem.AutoIncrement = true;
    //        dtItems.Columns.Add(dcItem);
    //        //HoTen column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "HoTen";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //DienThoai column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "DienThoai";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //DiaChiThu column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "DiaChiThu";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //GioiTinh column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "GioiTinh";
    //        dcItem.DataType = System.Type.GetType("System.Boolean"); dtItems.Columns.Add(dcItem);
    //        //NgaySinh column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "NgaySinh";
    //        dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
    //        //Email column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "Email";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //DiLam column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "DiLam";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //NguoiDungID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "NguoiDungID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        //MaHocVien column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "MaHocVien";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //NguoiTimNguonID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "NguoiTimNguonID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        //NguonID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "NguonID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        //GhiChuNguon column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "GhiChuNguon";
    //        dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //LevelID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "LevelID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        //TrangThaiGoiID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "TrangThaiGoiID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        //NgayGoi column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "NgayGoi";
    //        dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
    //        ////NgayDangKy column
    //        //dcItem = new DataColumn(); dcItem.ColumnName = "NgayDangKy";
    //        //dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
    //        ////MaTinh column
    //        //dcItem = new DataColumn(); dcItem.ColumnName = "MaTinh";
    //        //dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
    //        //TruongID column
    //        dcItem = new DataColumn(); dcItem.ColumnName = "TruongID";
    //        dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
    //        // make PK column 
    //        DataColumn[] pk = { dtItems.Columns[0] };
    //        dtItems.PrimaryKey = pk;
    //        // get new row to be added to the datatable
    //        DataRow drItem = dtItems.NewRow();

    //        DataRowCollection drs = xlTDS.Tables[0].Rows;
    //        int iCounter = 0;
    //        if (check)
    //        {
    //            foreach (DataRow dr in drs)
    //            {

    //                drItem["ID"] = iCounter++;
    //                drItem["HoTen"] = dr[CSTT_COT_IMPORT_EXCEL.HO_TEN].ToString().Trim();
    //                if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_NHA_RIENG].ToString().Trim().Equals(""))
    //                    drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_NHA_RIENG].ToString().Trim() + "/" + dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_DI_DONG].ToString().Trim();
    //                else drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_DI_DONG].ToString().Trim();
    //                drItem["DiaChiThu"] = dr[CSTT_COT_IMPORT_EXCEL.DIA_CHI_THUONG_TRU].ToString().Trim();
    //                if (!dr[CSTT_COT_IMPORT_EXCEL.GIOI_TINH].ToString().Trim().Equals(""))
    //                {
    //                    if (dr[CSTT_COT_IMPORT_EXCEL.GIOI_TINH].ToString().Trim().ToUpper().Equals("NAM"))
    //                    {
    //                        drItem["GioiTinh"] = true;
    //                    }
    //                    else
    //                    {
    //                        drItem["GioiTinh"] = false;
    //                    }
    //                }
    //                else
    //                {
    //                    drItem["GioiTinh"] = true;
    //                }

    //                if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim().Equals(""))
    //                {
    //                    try
    //                    {
    //                        drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture(transform_string_vi_date_2_en_date(dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim()), "vi-VN");
    //                        //drItem["NgaySinh"] = dr[3].ToString().Trim();
    //                    }
    //                    catch
    //                    {
    //                        drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                    }
    //                }
    //                else
    //                {
    //                    drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                }
    //                //if (!dr[21].ToString().Trim().Equals(""))
    //                //{
    //                //    try
    //                //    {
    //                //        drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture(transform_string_vi_date_2_en_date(dr[21].ToString().Trim()), "vi-VN");
    //                //    }
    //                //    catch
    //                //    {
    //                //        drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                //    }
    //                //}
    //                //else
    //                //{
    //                //    drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                //}
    //                //drItem["MaTinh"] = dr[cs].ToString().Trim();
    //                drItem["Email"] = dr[CSTT_COT_IMPORT_EXCEL.EMAIL].ToString().Trim();
    //                drItem["DiLam"] = dr[CSTT_COT_IMPORT_EXCEL.NGANH].ToString().Trim();
    //                //drItem["NguoiDungID"] = 1;
    //                drItem["MaHocVien"] = "";
    //                drItem["NguoiTimNguonID"] = NguoiTimNguonID;
    //                drItem["NguonID"] = Convert.ToInt32("1");
    //                drItem["GhiChuNguon"] = "";
    //                drItem["LevelID"] = Convert.ToInt32("1");
    //                drItem["TrangThaiGoiID"] = Convert.ToInt32("1");
    //                if (!dr[CSTT_COT_IMPORT_EXCEL.TEN_TRUONG].ToString().Trim().Equals(""))
    //                {
    //                    if (dr[CSTT_COT_IMPORT_EXCEL.TEN_TRUONG].ToString().Equals("DTU")) drItem["TruongID"] = ConstantVariables.TruongDTU;
    //                }
    //                else
    //                {
    //                    drItem["TruongID"] = ConstantVariables.TruongCHUA_BIET;
    //                }

    //                // add to ModuleIDle
    //                dtItems.Rows.Add(drItem);
    //                // create new row
    //                drItem = dtItems.NewRow();
    //            }
    //        }
    //        else
    //        {
    //            foreach (DataRow dr in drs)
    //            {

    //                drItem["ID"] = iCounter++;
    //                drItem["HoTen"] = dr[CSTT_COT_IMPORT_EXCEL.HO_TEN].ToString().Trim();
    //                if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_NHA_RIENG].ToString().Trim().Equals(""))
    //                    drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_NHA_RIENG].ToString().Trim() + "/" + dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_DI_DONG].ToString().Trim();
    //                else drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI_DI_DONG].ToString().Trim();
    //                drItem["DiaChiThu"] = dr[CSTT_COT_IMPORT_EXCEL.DIA_CHI_THUONG_TRU].ToString().Trim();
    //                if (!dr[CSTT_COT_IMPORT_EXCEL.GIOI_TINH].ToString().Trim().Equals(""))
    //                {
    //                    if (dr[CSTT_COT_IMPORT_EXCEL.GIOI_TINH].ToString().Trim().ToUpper().Equals("NAM"))
    //                    {
    //                        drItem["GioiTinh"] = true;
    //                    }
    //                    else
    //                    {
    //                        drItem["GioiTinh"] = false;
    //                    }
    //                }
    //                else
    //                {
    //                    drItem["GioiTinh"] = true;
    //                }

    //                if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim().Equals(""))
    //                {
    //                    try
    //                    {
    //                        drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture(transform_string_vi_date_2_en_date(dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim()), "vi-VN");
    //                        //drItem["NgaySinh"] = dr[3].ToString().Trim();
    //                    }
    //                    catch
    //                    {
    //                        drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                    }
    //                }
    //                else
    //                {
    //                    drItem["NgaySinh"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                }
    //                //if (!dr[21].ToString().Trim().Equals(""))
    //                //{
    //                //    try
    //                //    {
    //                //        drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture(transform_string_vi_date_2_en_date(dr[21].ToString().Trim()), "vi-VN");
    //                //    }
    //                //    catch
    //                //    {
    //                //        drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                //    }
    //                //}
    //                //else
    //                //{
    //                //    drItem["NgayDangKy"] = DateTimeUntil.ConvertDateCulture("01/01/1900", "vi-VN");
    //                //}
    //                drItem["Email"] = dr[CSTT_COT_IMPORT_EXCEL.EMAIL].ToString().Trim();
    //               // drItem["MaTinh"] = dr[18].ToString().Trim();
    //                drItem["DiLam"] = dr[CSTT_COT_IMPORT_EXCEL.NGHE_NGHIEP].ToString().Trim();
    //                //drItem["NguoiDungID"] = Convert.ToInt32("2");
    //                drItem["MaHocVien"] = "";
    //                drItem["NguoiTimNguonID"] = NguoiTimNguonID;
    //                drItem["NguonID"] = Convert.ToInt32("1");
    //                drItem["GhiChuNguon"] = "";
    //                drItem["LevelID"] = Convert.ToInt32("1");
    //                drItem["TrangThaiGoiID"] = Convert.ToInt32("1");

    //                if (!dr[CSTT_COT_IMPORT_EXCEL.TEN_TRUONG].ToString().Trim().Equals(""))
    //                {
    //                    if (dr[CSTT_COT_IMPORT_EXCEL.TEN_TRUONG].ToString().Equals("DTU")) drItem["TruongID"] = ConstantVariables.TruongDTU;
    //                    else if (dr[CSTT_COT_IMPORT_EXCEL.TEN_TRUONG].ToString().Equals("HOU")) drItem["TruongID"] = ConstantVariables.TruongCHUA_BIET;
    //                }
    //                else
    //                {
    //                    drItem["TruongID"] = ConstantVariables.TruongCHUA_BIET;
    //                }

    //                // add to ModuleIDle
    //                dtItems.Rows.Add(drItem);
    //                // create new row
    //                drItem = dtItems.NewRow();
    //            }
    //        }
    //        return dtItems;
    //    }
    //    catch (Exception e)
    //    {
    //        throw new Exception("Error while reading data from excel sheet", e);
    //    }
    //}
    public static DataSet getXLData(string xlSheetName, string xlFileName, string AdditionalFields)
    {
        try
        {
            string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbConnection xlConn = new OleDbConnection(connstr);
            DataSet xlTDS = new DataSet("xlDataSet");
            xlConn.Open();
            OleDbDataAdapter xlDA = new OleDbDataAdapter("Select" + AdditionalFields + " * from [" + xlSheetName + "$] ", xlConn);
            xlDA.Fill(xlTDS);
            xlConn.Close();
            xlConn.Dispose();

            RemoveEmptyRows(xlTDS.Tables[0], (AdditionalFields.Length - AdditionalFields.ToLower().Replace(" as ", "").Length) / 4);
            return xlTDS;
        }
        catch (Exception e)
        {
            throw new Exception("Error while reading data from excel sheet", e);
        }
    }

    public static DataTable getXLDataCRM(string xlSheetName, string xlFileName, string AdditionalFields, bool check, int NguoiTimNguonID)
    {
        try
        {
            string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbConnection xlConn = new OleDbConnection(connstr);
            DataSet xlTDS = new DataSet("xlDataSet");
            xlConn.Open();
            OleDbDataAdapter xlDA = new OleDbDataAdapter("Select" + AdditionalFields + " * from [" + xlSheetName + "$] ", xlConn);
            xlDA.Fill(xlTDS);
            xlConn.Close();
            xlConn.Dispose();
            RemoveEmptyRows(xlTDS.Tables[0], (AdditionalFields.Length - AdditionalFields.ToLower().Replace(" as ", "").Length) / 4);

            #region Tạo ra bảng và các cột tương ứng
            // create new table and call it rssItems
            DataTable dtItems = new DataTable("rssItems");
            DataColumn dcItem = new DataColumn(); dcItem.ColumnName = "ID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dcItem.AutoIncrement = true;
            dtItems.Columns.Add(dcItem);
            //HoTen column
            dcItem = new DataColumn(); dcItem.ColumnName = "HoTen";  // Họ và tên
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //DienThoai column
            dcItem = new DataColumn(); dcItem.ColumnName = "DienThoai"; // Điện thoại
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //DiaChiThu column
            dcItem = new DataColumn(); dcItem.ColumnName = "DiaChiThu"; // Nơi sống
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            ////GioiTinh column
            //dcItem = new DataColumn(); dcItem.ColumnName = "GioiTinh";
            //dcItem.DataType = System.Type.GetType("System.Boolean"); dtItems.Columns.Add(dcItem);
            ////NgaySinh column
            //dcItem = new DataColumn(); dcItem.ColumnName = "NgaySinh";
            //dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
            //Email column
            dcItem = new DataColumn(); dcItem.ColumnName = "Email"; // Email
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //DiLam column
            dcItem = new DataColumn(); dcItem.ColumnName = "DiLam";  // Đã tốt nghiệp
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //NguoiDungID column
            dcItem = new DataColumn(); dcItem.ColumnName = "NguoiDungID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            //MaHocVien column
            dcItem = new DataColumn(); dcItem.ColumnName = "MaHocVien";
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //NguoiTimNguonID column
            dcItem = new DataColumn(); dcItem.ColumnName = "NguoiTimNguonID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            //NguonID column
            dcItem = new DataColumn(); dcItem.ColumnName = "NguonID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            //GhiChuNguon column
            dcItem = new DataColumn(); dcItem.ColumnName = "GhiChuNguon"; // Ngành muốn học
            dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);
            //LevelID column
            dcItem = new DataColumn(); dcItem.ColumnName = "LevelID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            //TrangThaiGoiID column
            dcItem = new DataColumn(); dcItem.ColumnName = "TrangThaiGoiID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            //NgayGoi column
            dcItem = new DataColumn(); dcItem.ColumnName = "NgayGoi";
            dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
            ////NgayDangKy column
            //dcItem = new DataColumn(); dcItem.ColumnName = "NgayDangKy";
            //dcItem.DataType = System.Type.GetType("System.DateTime"); dtItems.Columns.Add(dcItem);
            ////MaTinh column
            //dcItem = new DataColumn(); dcItem.ColumnName = "MaTinh";
            //dcItem.DataType = System.Type.GetType("System.String"); dtItems.Columns.Add(dcItem);

            //TruongID column
            dcItem = new DataColumn(); dcItem.ColumnName = "TruongID";
            dcItem.DataType = System.Type.GetType("System.Int32"); dtItems.Columns.Add(dcItem);
            // make PK column 
            DataColumn[] pk = { dtItems.Columns[0] };
            dtItems.PrimaryKey = pk;
            #endregion

            #region Đọc dữ liệu từ file excel và cho vào bảng đó
            // get new row to be added to the datatable
            DataRow drItem = dtItems.NewRow();

            DataRowCollection drs = xlTDS.Tables[0].Rows;
            int iCounter = 0;
            if (check)
            {
                foreach (DataRow dr in drs)
                {

                    drItem["ID"] = iCounter++;
                    drItem["HoTen"] = dr[CSTT_COT_IMPORT_EXCEL.HO_TEN].ToString().Trim();
                    if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim().Equals(""))
                        drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim();

                    drItem["DiaChiThu"] = dr[CSTT_COT_IMPORT_EXCEL.NOI_SONG].ToString().Trim();
                    drItem["Email"] = dr[CSTT_COT_IMPORT_EXCEL.EMAIL].ToString().Trim();
                    drItem["DiLam"] = dr[CSTT_COT_IMPORT_EXCEL.DA_TOT_NGHIEP_TRUONG].ToString().Trim();
                    drItem["MaHocVien"] = "";
                    drItem["NguoiTimNguonID"] = NguoiTimNguonID;
                    drItem["NguonID"] = Convert.ToInt32("1");
                    drItem["GhiChuNguon"] = dr[CSTT_COT_IMPORT_EXCEL.NGANH_MUON_HOC].ToString().Trim(); ;
                    drItem["LevelID"] = Convert.ToInt32("1");
                    drItem["TrangThaiGoiID"] = Convert.ToInt32("1");
                    // add to ModuleIDle
                    dtItems.Rows.Add(drItem);
                    // create new row
                    drItem = dtItems.NewRow();
                }
            }
            else
            {
                foreach (DataRow dr in drs)
                {

                    drItem["ID"] = iCounter++;
                    drItem["HoTen"] = dr[CSTT_COT_IMPORT_EXCEL.HO_TEN].ToString().Trim();
                    if (!dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim().Equals(""))
                        drItem["DienThoai"] = dr[CSTT_COT_IMPORT_EXCEL.DIEN_THOAI].ToString().Trim();
                    drItem["DiaChiThu"] = dr[CSTT_COT_IMPORT_EXCEL.NOI_SONG].ToString().Trim();
                    drItem["Email"] = dr[CSTT_COT_IMPORT_EXCEL.EMAIL].ToString().Trim();
                    drItem["DiLam"] = dr[CSTT_COT_IMPORT_EXCEL.DA_TOT_NGHIEP_TRUONG].ToString().Trim();
                    drItem["MaHocVien"] = "";
                    drItem["NguoiTimNguonID"] = NguoiTimNguonID;
                    drItem["NguonID"] = Convert.ToInt32("1");
                    drItem["GhiChuNguon"] = dr[CSTT_COT_IMPORT_EXCEL.NGANH_MUON_HOC].ToString().Trim(); ;
                    drItem["LevelID"] = Convert.ToInt32("1");
                    drItem["TrangThaiGoiID"] = Convert.ToInt32("1");
                    // add to ModuleIDle
                    dtItems.Rows.Add(drItem);
                    // create new row
                    drItem = dtItems.NewRow();
                }
            }
            #endregion
           
            return dtItems;
        }
        catch (Exception e)
        {
            throw new Exception("Error while reading data from excel sheet", e);
        }
    }


    public static void RemoveEmptyRows(DataTable dtbl, System.Int32 intNumberOfFieldsToIgnore)
    {
        System.String strFilter = "";
        //Check at least 3/4th of the columns for null value
        System.Int32 intAvgColsToCheck = Convert.ToInt32((dtbl.Columns.Count - intNumberOfFieldsToIgnore) * 0.75);
        //Can't entertain checking less than three columns.
        if (intAvgColsToCheck < 3)
        {
            intAvgColsToCheck = dtbl.Columns.Count;
        }

        //Building the filter string that checks null value in 3/4th of the total column numbers...

        //We will be doing it in reverse, checking the last three-quarter columns
        System.Int32 lngEnd = dtbl.Columns.Count;
        lngEnd = lngEnd - intAvgColsToCheck;
        for (int lngStartColumn = dtbl.Columns.Count; lngStartColumn > lngEnd; lngStartColumn--)
        {
            strFilter += "[" + dtbl.Columns[lngStartColumn - 1].ColumnName + "] IS NULL AND "; //AND to concatenate the next column in the filter
        }

        //Remove the trailing AND
        if (strFilter.Length > 1) //At least one column is added (and thus, the trailing AND)
        {
            strFilter = strFilter.Remove(strFilter.Length - 4);
        }
        DataRow[] drows = dtbl.Select(strFilter);

        //Remove the rows that are empty...
        foreach (DataRow drow in drows)
        {
            dtbl.Rows.Remove(drow);
        }
    }

    public static void BulkCopy(DataTable dtTable, SqlConnection SqlConn1, string strTableName)
    {
        SqlConn1.Open();
        using (SqlBulkCopy bc = new SqlBulkCopy((SqlConnection)SqlConn1))
        {
            bc.DestinationTableName = strTableName;
            bc.WriteToServer(dtTable);
            bc.Close();
        }
        SqlConn1.Close();
    }

    #endregion

    #region ExportToMultipleExcelSheets
    private static System.Text.StringBuilder SqlScript = new System.Text.StringBuilder();
    private static System.Text.StringBuilder SqlInsert = new System.Text.StringBuilder();
    private static bool mPredefineFile = false;

    public static void ImportToMultipleXLSheets(System.String SqlSelect, System.String mOutputFileName, SqlConnection SqlConn)
    {
        string FolderPath;
        FolderPath = mOutputFileName.Remove(mOutputFileName.LastIndexOf("\\"), mOutputFileName.Length - mOutputFileName.LastIndexOf("\\"));
        File.Copy(FolderPath + "\\Sample.xls", mOutputFileName, true);
        SqlConn.Open();
        DataSet DS = new DataSet();
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mOutputFileName + ";Extended Properties='Excel 8.0'";
        OleDbConnection xlConn = new OleDbConnection(connstr);

        try
        {
            xlConn.Open();

            SqlDataAdapter SqlDA = new SqlDataAdapter(SqlSelect, SqlConn);
            SqlDA.Fill(DS);
            SqlConn.Close();
            SqlConn.Dispose();
            PrepareScript(DS.Tables[0]);
            StartImport(DS.Tables[0], xlConn);

        }
        catch (Exception exp)
        {
            throw new Exception("ImportToMultipleXLSheets", exp.InnerException);
        }
        finally
        {
            if (xlConn != null)
            {
                if (xlConn.State == ConnectionState.Open) xlConn.Close();
                xlConn.Dispose();
            }
            if (SqlConn != null)
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
                SqlConn.Dispose();
            }
        }
    }
    private static void CreateXLSheets(DataTable DTable, OleDbConnection xlConn, System.String XLSheetName)
    {
        // Create New Excel Sheet

        System.Text.StringBuilder SqlFinalScript = new System.Text.StringBuilder();
        OleDbCommand cmdXl = new OleDbCommand();
        try
        {

            SqlFinalScript.Length = 0;

            cmdXl.Connection = xlConn;

            SqlFinalScript.Append("CREATE TABLE " + XLSheetName + " (");
            SqlFinalScript.Append(SqlScript.ToString());

            cmdXl.CommandText = SqlFinalScript.ToString();
            cmdXl.ExecuteNonQuery();

        }
        catch (Exception xlSheetExp)
        {
            throw (new Exception("CreateXLSheetException", xlSheetExp.InnerException));
        }
        finally
        {
            cmdXl.Dispose();
        }
    }

    private static string PrepareScript(DataTable DTable)
    {
        // Prepare Scripts to create excel Sheet

        SqlInsert.Length = 0;
        SqlScript.Length = 0;
        for (int i = 0; i < DTable.Columns.Count; i++)
        {
            SqlInsert.Append("[" + DTable.Columns[i].ColumnName + "],");

            SqlScript.Append("[" + DTable.Columns[i].ColumnName.Replace("'", "''") + "]");

            if (DTable.Columns[i].DataType.ToString().ToLower().Contains("int") || DTable.Columns[i].DataType.ToString().ToLower().Contains("decimal"))
                SqlScript.Append(" double");
            else
                SqlScript.Append(" text");

            SqlScript.Append(", ");
        }
        SqlInsert.Remove(SqlInsert.Length - 1, 1);
        SqlScript.Remove(SqlScript.Length - 2, 1);
        SqlScript.Append(") ");
        return SqlScript.ToString();
    }
    private static void StartImport(DataTable DTable, OleDbConnection xlConn)
    {
        Int64 rowNo = 0, xlSheetIndex = 2, TotalNoOfRecords = 0;

        System.String NewXLSheetName = "Sheet";
        System.Text.StringBuilder strInsert = new System.Text.StringBuilder();
        TotalNoOfRecords = DTable.Rows.Count;
        OleDbCommand cmdXl = new OleDbCommand();
        cmdXl.Connection = xlConn;
        if (mPredefineFile) xlSheetIndex = 1;
        for (int count = 0; count < DTable.Rows.Count; count++)
        {
            strInsert.Length = 0;

            if (rowNo == 0 && !mPredefineFile)
                CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
            rowNo += 1;

            // TotalNoOfRecords : Total no of records return by Sql Query, ideally should be set to 65535
            //rowNo : current Row no in the loop 
            if (TotalNoOfRecords > 5000 && rowNo > 5000)
            {
                xlSheetIndex += 1;
                if (!mPredefineFile) CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex);
                rowNo = 1;
            }
            strInsert.Append("Insert Into [" + NewXLSheetName + xlSheetIndex.ToString() + "$](" + SqlInsert.ToString() + ") Values (");
            foreach (DataColumn dCol in DTable.Columns)
            {
                if (dCol.DataType.ToString().ToLower().Contains("int"))
                {
                    if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                        strInsert.Append("NULL");
                    else
                        strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
                }
                else if (dCol.DataType.ToString().ToLower().ToLower().Contains("decimal"))
                {
                    if (DTable.Rows[count][dCol.Ordinal].ToString() == "")
                        strInsert.Append("NULL");
                    else
                        strInsert.Append(DTable.Rows[count][dCol.Ordinal]);
                }
                else
                    strInsert.Append("\"" + DTable.Rows[count][dCol.Ordinal].ToString().Replace("'", "''") + "\"");

                strInsert.Append(",");
            }
            strInsert.Remove(strInsert.Length - 1, 1);
            strInsert.Append(");");
            cmdXl.CommandText = strInsert.ToString();
            cmdXl.ExecuteNonQuery();
        }
    }

    #endregion
}
